//
//  CLStatusDAL.swift
//  CLWeibo
//
//  Created by 陈亮 on 16/7/23.
//  Copyright © 2016年 陈亮. All rights reserved.
//
import UIKit

//  删除七天前的数据
let MAXTime: Double = -(60 * 60 * 24 * 7)

//  数据库访问层, 用于缓存数据及网络请求
class CLStatusDAL: NSObject {
    // 加载数据
    class func loadData(maxId: Int64, sinceId: Int64, callBack: (statuses: [[String: AnyObject]])->()) {
        
        //  1.检查本地是否有缓存数据 (完成)
        
        let result = checkCacheData(maxId, sinceId: sinceId)
        
        //  2.如果有缓存数据之间返回缓存数据
        if result.count > 0 {
            callBack(statuses: result)
            return
        }
        
        let tempArray = [[String: AnyObject]]()
        //  3.如果没有缓存数据从网络加载
        CLNetWorkingTool.sharedTool.loadStatus(maxId, sinceId: sinceId) { (response, error) -> () in
            if error != nil {
                print(error)
                callBack(statuses: tempArray)
                return
            }
            //  代码执行到此,是成功的回调
            
            guard let dic = response as? [String: AnyObject] else {
                print("不是一个正确的字典格式")
                callBack(statuses: tempArray)
                return
            }
            
            //  代码执行到此字典格式正确
            
            guard let statusDicArray = dic["statuses"] as? [[String: AnyObject]] else {
                print("不是一个正确的字典格式")
                callBack(statuses: tempArray)
                return
            }
            //  4.网络加载后的数据缓存到本地 (完成)
            CLStatusDAL.cacheData(statusDicArray)
            
            // 5.数据缓存成功后,返回网络加载后的数据
            callBack(statuses: statusDicArray)
        }
        
        
        
        
        
        
        
        
    }
    
    //  通过maxid和sinceid去本地查询数据
    class func checkCacheData(maxId: Int64, sinceId: Int64) -> [[String: AnyObject]] {
        //SELECT statusid, status, userid from statuses where statusid > 4000071387627663 and userid = 1800530611 order by statusid desc limit 20
        var sql = "SELECT statusid, status, userid from statuses\n"
        //  上拉加载
        if maxId > 0 {
            sql += "WHERE STATUSID < \(maxId)\n"
        } else {
            //上拉加载 或者第一次请求
            sql += "WHERE STATUSID > \(sinceId)\n"
        }
        //  拼接userid
        sql += "and userid = \(CLUserAccountViewModel.sharedUserAccount.userAccount!.uid)\n"
        // 排序方式 statusid 降序
        sql += "order by statusid desc\n"
        // 每次获取20条数据
        sql += "limit 20\n"
        //  查询本地数据
        let result = CLSqliteManager.manager.queryResultSet(sql)
        
        var tempArray = [[String: AnyObject]]()
        for value in result {
            //  获取微博字典二进制数据
            let statusData = value["status"]! as! NSData
            //  取到微博缓存数据的字典
            let statusDic = try! NSJSONSerialization.JSONObjectWithData(statusData, options: []) as! [String: AnyObject]
            tempArray.append(statusDic)
        }
        return tempArray
        
    }
    
    
    //  缓存服务端返回给我们数组字典
    class func cacheData(statuses: [[String: AnyObject]]) {
        
        //  准备sql语句
        
        let sql = "INSERT INTO STATUSES(STATUSID, STATUS, USERID) VALUES(?, ?, ?)"
        //  获取用户id
        let userid = CLUserAccountViewModel.sharedUserAccount.userAccount!.uid
        //  执行sql语句
        CLSqliteManager.manager.queue.inTransaction { (db, rollBack) -> Void in
            
            for status in statuses {
                //  获取微博id
                let statusid = status["id"]!
                //  微博数据
                let statusData = try! NSJSONSerialization.dataWithJSONObject(status, options: [])
                //  执行sql语句
                let result = db.executeUpdate(sql, withArgumentsInArray: [statusid, statusData, "\(userid)"])
                if result == false {
                    rollBack.memory = true
                    break
                }
                
            }
            
            
            
        }
        
        
        
    }
    
    //  清空缓存数据
    class func clearCacheData() {
        //  准备sql  SELECT * from statuses where time < '2016-07-22 17:09:33'
        
        let dt = NSDateFormatter()
        dt.dateFormat = "yyyy-MM-dd HH:mm:ss"
        dt.locale = NSLocale(localeIdentifier: "en_US")
        //  获取5分钟之前的时间
        let time = NSDate().dateByAddingTimeInterval(MAXTime)
        //  获取时间字符串
        let timeStr = dt.stringFromDate(time)
        //  准备sql语句
        let sql = "DELETE FROM STATUSES WHERE TIME < '\(timeStr)'"
        
        //  执行sql语句
        CLSqliteManager.manager.queue.inDatabase { (db) -> Void in
            let result = db.executeUpdate(sql, withArgumentsInArray: nil)
            if result {
                //db.changes() ,执行sql语句后会影响条数
                print("缓存数据删除成功,影响了\(db.changes())条数据")
                
            } else {
                print("缓存数据删除失败")
            }
        }
        
        
    }
    
    
    
    
    
    
    
    
    
    
    
    
    
}
